package com.newbie.iSee;

import java.io.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;
import android.widget.Toast;

public class LocalDatabase {
	private Context mContext;
	private SQLiteDatabase mDatabase;

	private static final String DB_PATH = "/data/data/com.newbie.iSee/databases/local_database.sqlite";
	private static final String DB_FILE = "local_database.sqlite";
	
	private DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");

	public static class Album {
		public long id;
		public String name;
		public Date created_time;
		public boolean online;
	}

	public static class Photo {
		public long id;
		public long album_id;
		public String desc;
		public String source;
		public int latitude;
		public int longitude;
		public Date created_time;
		public boolean online;
	}

	public static class Sound {
		public long id;
		public long photo_id;
		public String source;
		public Date created_time;
		public boolean online;
	}

	public LocalDatabase(Context context) {
		mContext = context;
	}

	public void load() {
		try {
			mDatabase = SQLiteDatabase.openDatabase(DB_PATH, null,
					SQLiteDatabase.OPEN_READWRITE);
			
			Log.d("LocalDatabase", "loaded local database");
		} catch (SQLiteException e) {
			mDatabase = mContext.openOrCreateDatabase(DB_FILE,
					SQLiteDatabase.CREATE_IF_NECESSARY, null);
			mDatabase.close();
			copy();
			mDatabase = SQLiteDatabase.openDatabase(DB_PATH, null,
					SQLiteDatabase.OPEN_READWRITE);
			Log.d("LocalDatabase", "created local database");
		}
	}

	private void copy() {
		try {
			InputStream in = mContext.getAssets().open(DB_FILE);
			OutputStream out = new FileOutputStream(DB_PATH);

			byte[] buf = new byte[1024];
			int len;
			while ((len = in.read(buf)) > 0) {
				out.write(buf, 0, len);
			}

			out.close();
			in.close();
		} catch (IOException e) {
			Toast.makeText(mContext, e.toString(), Toast.LENGTH_LONG).show();
		}
	}

	// SELECTs

	public Album[] getAlbums() {
		String query = "SELECT * FROM albums ORDER BY created_time DESC";
		Cursor result = mDatabase.rawQuery(query, null);
		Log.d("LocalDatabase", "'" + query + "' gets " + result.getCount()
				+ " result(s)");

		Album[] albums = new Album[result.getCount()];
		result.moveToFirst();
		for (int i = 0; i < result.getCount(); i++) {
			albums[i] = new Album();
			albums[i].id = result.getLong(0);
			albums[i].name = result.getString(1);
			try {
				albums[i].created_time = dateFormat.parse(result.getString(2));
			} catch(ParseException pe) {
				albums[i].created_time = new Date();
			}
			albums[i].online = result.getInt(3)==1;
			result.moveToNext();
		}
		result.close();
		return albums;
	}

	public Photo[] getPhotos(long album_id) {
		String query = "SELECT * FROM photos WHERE album_id=" + album_id;
		Cursor result = mDatabase.rawQuery(query, null);
		Log.d("LocalDatabase", "'" + query + "' gets " + result.getCount()
				+ " result(s)");

		Photo[] photos = new Photo[result.getCount()];
		result.moveToFirst();
		for (int i = 0; i < result.getCount(); i++) {
			photos[i] = new Photo();
			photos[i].id = result.getLong(0);
			photos[i].album_id = result.getLong(1);
			photos[i].desc = result.getString(2);
			photos[i].source = result.getString(3);
			photos[i].latitude = result.getInt(4);
			photos[i].longitude = result.getInt(5);
			try {
				photos[i].created_time = dateFormat.parse(result.getString(6));
			} catch(ParseException pe) {
				photos[i].created_time = new Date();
			}
			photos[i].online = result.getInt(7)==1;
			result.moveToNext();
		}
		result.close();
		return photos;
	}

	public Sound[] getSounds(long photo_id) {
		String query = "SELECT * FROM sounds WHERE photo_id=" + photo_id;
		Cursor result = mDatabase.rawQuery(query, null);
		Log.d("LocalDatabase", "'" + query + "' gets " + result.getCount()
				+ " result(s)");

		Sound[] sounds = new Sound[result.getCount()];
		result.moveToFirst();
		for (int i = 0; i < result.getCount(); i++) {
			sounds[i] = new Sound();
			sounds[i].id = result.getLong(0);
			sounds[i].photo_id = result.getLong(1);
			sounds[i].source = result.getString(2);
			try {
				sounds[i].created_time = dateFormat.parse(result.getString(3));
			} catch(ParseException pe) {
				sounds[i].created_time = new Date();
			}
			sounds[i].online = result.getInt(4)==1;
			result.moveToNext();
		}
		result.close();
		return sounds;
	}

	// TABLE albums INSERT/DELETE/UPDATE

	private ContentValues albumValues(Album album) {
		ContentValues values = new ContentValues();
		//values.put("id", album.id);
		values.put("name", album.name);
		values.put("created_time", dateFormat.format(album.created_time));
		values.put("online", album.online?1:0);
		return values;
	}

	public void addAlbum(Album album) {
		if(album.created_time == null)
			album.created_time = new Date();
		ContentValues values = albumValues(album);
		album.id = mDatabase.insert("albums", null, values);
		Log.d("LocalDatabase", "addAlbum");
	}

	public void removeAlbum(long photo_id) {
		String[] args = new String[] { "" + photo_id };
		mDatabase.delete("albums", "id=?", args);
		Log.d("LocalDatabase", "removeAlbum");
	}

	public void updateAlbum(Album album) {
		ContentValues values = albumValues(album);
		String[] args = new String[] { "" + album.id };
		mDatabase.update("albums", values, "id=?", args);
		Log.d("LocalDatabase", "updateAlbum");
	}

	// TABLE photos INSERT/DELETE/UPDATE

	private ContentValues photoValues(Photo photo) {
		ContentValues values = new ContentValues();
		//values.put("id", photo.id);
		values.put("album_id", photo.album_id);
		values.put("desc", photo.desc);
		values.put("source", photo.source);
		values.put("latitude", photo.latitude);
		values.put("longitude", photo.longitude);
		values.put("created_time", dateFormat.format(photo.created_time));
		values.put("online", photo.online?1:0);
		return values;
	}

	public void addPhoto(Photo photo) { // will give new id
		if(photo.created_time == null)
			photo.created_time = new Date();
		ContentValues values = photoValues(photo);
		photo.id = mDatabase.insert("photos", null, values);
		Log.d("LocalDatabase", "addPhoto");
	}

	public void removePhoto(long photo_id) {
		String[] args = new String[] { "" + photo_id };
		mDatabase.delete("photos", "id=?", args);
		Log.d("LocalDatabase", "removePhoto");
	}

	public void updatePhoto(Photo photo) {
		ContentValues values = photoValues(photo);
		String[] args = new String[] { "" + photo.id };
		mDatabase.update("photos", values, "id=?", args);
		Log.d("LocalDatabase", "updatePhoto");
	}

	// TABLE sounds INSERT/DELETE/UPDATE

	private ContentValues soundValues(Sound sound) {
		ContentValues values = new ContentValues();
		//values.put("id", sound.id);
		values.put("photo_id", sound.photo_id);
		values.put("source", sound.source);
		values.put("created_time", dateFormat.format(sound.created_time));
		values.put("online", sound.online?1:0);
		return values;
	}

	public void addSound(Sound sound) { // will give new id
		if(sound.created_time == null)
			sound.created_time = new Date();
		ContentValues values = soundValues(sound);
		sound.id = mDatabase.insert("sounds", null, values);
		Log.d("LocalDatabase", "addSound");
	}

	public void removeSound(long sound_id) {
		String[] args = new String[] { "" + sound_id };
		mDatabase.delete("sounds", "id=?", args);
		Log.d("LocalDatabase", "removeSound");
	}

	public void updateSound(Sound sound) {
		ContentValues values = soundValues(sound);
		String[] args = new String[] { "" + sound.id };
		mDatabase.update("sounds", values, "id=?", args);
		Log.d("LocalDatabase", "updateSound");
	}
}
